package excel;

import java.io.*;
import java.io.File;
import java.util.*;
import jxl.write.*;
import jxl.format.*;
import jxl.*;
import jxl.write.biff.*;

public class FileAnalyze {
	private int pre_Index;

	private int mid_Index;

	private int last_Index;

	private String pre_Str = "<a href=\"/science/journal";

	private String mid_Str = "\">";

	private String last_Str = "</a>";

	private String app_Str = "http://www.sciencedirect.com";

	public void createContent() {

		File f21 = new File("d:\\sql.txt");
		// if can not find the file
		if (f21.exists() == false) {
			System.out.println("ERROR: File not exist");
		} else {
			// create the excel
			try {

				FileReader fr1 = new FileReader(f21);
				BufferedReader br1 = new BufferedReader(fr1);
				String[] bb = new String[3];
				String[] ab = new String[1320];
				String[] a = new String[1320];

				for (int i = 0; i < 1320; i++) {
					String str1 = br1.readLine();
					ab[i] = str1;
					for (int j = 0; j < 1320; j++) {
						a[j] = ab[i].toString();

						// System.out.println(a[j]);

						int pre, mid, last;
						pre = a[j].indexOf(pre_Str);
						mid = a[j].indexOf(mid_Str);
						last = a[j].indexOf(last_Str);
						bb[0] = a[j].substring(pre + 26, mid);// ISSN
						bb[1] = a[j].substring(pre + 9, mid);// URL
						bb[2] = a[j].substring(mid + 2, last);// Name

						WorkbookSettings wbSettings = new WorkbookSettings();
						wbSettings.setLocale(new Locale("en", "EN"));
						WritableWorkbook workbook = Workbook.createWorkbook(
								new File("out.xls"), wbSettings);
						workbook.createSheet("book", 0);
						WritableSheet excelSheet = workbook.getSheet(0);

						addLabel(excelSheet, 0, 0, "ISSN");
						addLabel(excelSheet, 1, 0, "URL");
						addLabel(excelSheet, 2, 0, "NAME");

						addLabel(excelSheet, 0, j + 1, " " + bb[0] + " ");
						addLabel(excelSheet, 1, j + 1, app_Str + bb[1]);
						addLabel(excelSheet, 2, j + 1, bb[2]);

						workbook.write();
						workbook.close();
					}
				}
				for (int j = 0; j < 1320; j++) {
					// System.out.println("ab[" + j + "]= " + ab[j]);
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void addLabel(WritableSheet sheet, int col, int row, String str)
			throws WriteException, RowsExceededException {

		Label label;
		label = new Label(col, row, str);
		sheet.addCell(label);
	}

	public FileAnalyze() {
		FileAnalyze fileAnalyze=new FileAnalyze();
		fileAnalyze.createContent();
	}

}
